{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Table of Contents\n",
    "* [Lecture 2B - Introduction to Working with Data Frames](#Lecture-2B---Introduction-to-Working-with-Data-Frames)\n",
    "\t* &nbsp;\n",
    "\t\t* [Content](#Content)\n",
    "\t\t* [Learning Outcomes](#Learning-Outcomes)\n",
    "* [DataFrame](#DataFrame)\n",
    "\t* [Creating a Data Frame](#Creating-a-Data-Frame)\n",
    "\t\t* [Accessing data in a Data Frame](#Accessing-data-in-a-Data-Frame)\n",
    "\t\t\t* [By Column](#By-Column)\n",
    "\t\t\t* [By Row](#By-Row)\n",
    "\t\t\t* [By Row and Column](#By-Row-and-Column)\n",
    "\t\t* [Filtering Data](#Filtering-Data)\n",
    "\t* [Modifying Data Frames](#Modifying-Data-Frames)\n",
    "\t\t* [Adding New Columns](#Adding-New-Columns)\n",
    "\t* [Changing values](#Changing-values)\n",
    "\t\t* [Deleting rows (records) based on conditions](#Deleting-rows-%28records%29-based-on-conditions)\n",
    "\t\t* [Adding new rows/records](#Adding-new-rows/records)\n",
    "* [Data Cleaning - Practical Example](#Data-Cleaning---Practical-Example)\n",
    "\t* &nbsp;\n",
    "\t\t* [Exercise:](#Exercise:)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Lecture 2B - Introduction to Working with Data Frames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Content"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. What is a Data Frame\n",
    "2. Creating, Accessing, Filtering data in a Data Frame\n",
    "3. Modifying values in a Data Frames\n",
    "4. Deleting entries in a Data Frame\n",
    "5. Practical Example of Data Cleaning (Data Wrangling)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Learning Outcomes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At the end of this lecture, you should be able to:\n",
    "\n",
    "* describe the reasons for using the Data Frame data type  \n",
    "* create a data frame object from lists and dictionaries \n",
    "* access data based on location\n",
    "* filter data based on conditions\n",
    "* modify data frames by creating/deleting/change types of columns\n",
    "* modify data frames by changing values of individual data points\n",
    "* delete records from data frames based on a condition"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.core.display import HTML\n",
    "HTML(\"<iframe src=http://en.wikipedia.org/wiki/Pandas_%28software%29 width=800 height=350></iframe>\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import some extra plotting libraries that pretty-fy the plots \n",
    "import seaborn as sns\n",
    "from pylab import rcParams\n",
    "\n",
    "rcParams['figure.dpi'] = 350\n",
    "rcParams['lines.linewidth'] = 2\n",
    "rcParams['axes.facecolor'] = 'white'\n",
    "rcParams['patch.edgecolor'] = 'white'\n",
    "rcParams['font.family'] = 'StixGeneral'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set some Pandas options as you like\n",
    "pd.set_option('max_columns', 40)\n",
    "pd.set_option('max_rows', 20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#this line enables the plots to be embedded into the notebook\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Inevitably, we want to be able to store, view and manipulate data that is **multivariate**, where for every index there are multiple fields or columns of data, often of **varying data type**.\n",
    "\n",
    "A `DataFrame` is a **tabular data structure**, encapsulating multiple series like columns in a spreadsheet. Data are stored internally as a 2-dimensional object, but the `DataFrame` allows us to represent and manipulate higher-dimensional data.\n",
    "\n",
    "With the creation of pandas and in particular the `DataFrame` data structure, Python has gained the same power as the statistical software **R** and combined with the Numpy package, Python can now also do all that **MATLAB** can do, with the added advantage that Python is a general purpose language that can be used for any type of programming, while MATLAB and R are domain specific languages only."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before creating our first Data Frame object to represent a tabular data set, let us first clarify what the characteristics of a tabular dataset might look like. \n",
    "\n",
    "Each row will represent a single data point. There are going to be multiple columns in the dataset. Each column will hold an additional piece of information that describes some characteristic of a particular data point. Different disciplines have different names for the column component and this can unfortunately get confusing. The most common of these are: features, (independent) variables or attributes. Computer scientists prefer the term *features*, so this is what will mostly use here.\n",
    "\n",
    "In the example below, there is an expanded version of the population dataset seen in the previous illustrations, that includes a feature called 'YEAR'. "
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    " \tNATION \t        POPULATION \tYEAR\n",
    " \tNew Zealand \t3778000 \t2000\n",
    " \tAustralia \t    19138000 \t2000\n",
    " \tCook Islands \t20000 \t    2000\n",
    " \tSolomon Islands 447000 \t    2000\n",
    " \tNew Zealand \t4433000 \t2014\n",
    " \tAustralia \t    22680000 \t2014\n",
    "    Cook Islands \t10900 \t    2014\n",
    " \tSolomon Islands 549598 \t    2014"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Discussion exercise**: Given Python's data structures seen in previous examples, consider how they can be used to store the above data? Discuss your options with a colleague working with you."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "An intuitive approach would be to store each of the column values in a data structure such as a list and give it a suitable name. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nation = ['New Zealand', 'Australia', 'Cook Islands', 'Solomon Islands', 'New Zealand', 'Australia', 'Cook Islands', 'Solomon Islands']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Assign the values below to appropriately named variables for the next two features.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#population\n",
    "population = "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#year\n",
    "year = "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now have three lists. One list for each of the feature values.\n",
    "\n",
    "What we now need to do is to combine the three lists into a single data structure that will store all the values.\n",
    "\n",
    "That data structure is a dictionary."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Create a dictionary called *national_populations* from the above lists, where each of the keys, corresponds to the name of the features, and is associated with the appropriate list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "national_populations = "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With all the data stored inside the dictionary object, we can now proceed to create the Data Frame object, which offers us much more functionality, efficiency and expressive power."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating a Data Frame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The DataFrame can be created by placing a dictionary object into the DataFrame constructor, which is denoted by the parentheses."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.DataFrame(national_populations)\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data in a `DataFrame` can also be plotted in a similar fashion as with a `Series` object. The index becomes the x-axis. The y-axis is can be selected using the '.' operator:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.population.plot(kind='bar')\n",
    "plt.xticks(rotation=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice the `DataFrame` is sorted by column name. We can change the order by indexing them in the order we desire by passing in a list of column names:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[['nation','year','population']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A `DataFrame` has a second index, representing the columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "list(data.index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Accessing data in a Data Frame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### By Column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we wish to access columns, we can do so either by dictionary-like indexing or by feature name:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['population']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.population"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: check what type is returned when you call data.population and access it as a feature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: check what type is returned when you access [['population']] by passing it as a list to data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### By Row"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice in the above example how this differs in comparison with `Series`, where dict-like indexing retrieved a particular element (row). If we want to access a row in a `DataFrame`, we index its location attribute using `loc` or `iloc` depending on if we are using the index label name or explicit index position."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.loc[3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can do more sophisticated selection of data using slicing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.loc[4:6]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### By Row and Column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.loc[4:6,['year']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.loc[4:6, 'year':'nation']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.iloc[4:6,[0,2]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise:** Select and display only the first 4 rows of *data* and show only the 'nation' and 'population' features."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are a number of different ways to index dataframes. Refer to http://pandas.pydata.org/pandas-docs/stable/indexing.html#different-choices-for-indexing for an in depth description of options and performance profiles.\n",
    "\n",
    "Below is an example of performances for different ways of accessing dataframes that are highly relevant when the datasets become large:  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%timeit data.loc[0,'nation']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%timeit data.iloc[0,0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%timeit data.iat[0,0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filtering Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data Frames provide the functionality to select data based on specific conditions. This is called 'filtering'. \n",
    "\n",
    "There are a number of different ways of performing filtering."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[data.population > 1000000]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The most common is using a second set of square brackets, which contain the actual condition to be satisfied:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['population'][data['nation'] == 'New Zealand']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Often we need to perform filtering on multiple conditions.\n",
    "\n",
    "Below is an example of displaying the nation name and population of a country that has more than 1 million population on data after year 2000. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[['nation','population']][(data['population'] > 1000000) & (data['year'] > 2000)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that the multiple conditions must be enclosed within the parentheses. The symbol '&' signifies that both sides of the equations must be satisfied. Alternatively, the symbol '|' signifying or, can be used when either one of the conditions must be satisfied. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Write a filter that display the name and the year of countries which had populations greater than 2 million and smaller 20 million."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas is under continual development and improvement. The new and experimental functionality which simplifies the filtering is using the keyword *query*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[['year','nation']].query('year > 2000')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[['year','nation']].query('year > 2000 and nation in [\"Australia\", \"New Zealand\"]')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[['year','nation']].query('index > 4')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Conditional filtering on data frames can also be performed using the `where` construct, which preserves the 'shape' of the data and lists values that do not meet the criterion as a NaN:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['population'].where(data['nation'] == 'New Zealand')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Modifying Data Frames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding New Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "New columns can be created as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['region'] = ''\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Values can be assigned to new columns directly:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['region'] = 'Oceania'\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can create new columns automatically which are in some way derivatives of existing data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['risingSeaLevelRisk'] =  data['nation'].str.contains(\"Islands\")\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also derive new columns by applying a math operation to existing columns\n",
    "\n",
    "**Exercise**: Write code that creates a new column (call it sqrtPopulation) which holds the values of the square root of the population. The function can be accessed through np.sqrt()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A frequent task performed in data cleaning and analysis is modifying data types for columns. The below code shows how this can be done."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['sqrtPopulation'] = data['sqrtPopulation'].astype(int)\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Columns can be deleted as follows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "del data['sqrtPopulation']\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Changing values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can modify individual values in a data frame by indexing the location in a data frame using the ix attribute."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to change the risingSeaLevelRisk for NZ's first entry:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.iloc[0,4] = True\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Change the value for NZ's second risingSeaLevelRisk value from False to True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One of the most time consuming tasks is performing data cleaning, where the task frequently involves changing the values of strings in columns.\n",
    "\n",
    "The following code will change the name of 'New Zealand' to 'New Zealand (Aotearoa)'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['nation'] = data['nation'].str.replace('New Zealand', 'New Zealand (Aotearoa)')\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Write code that changes 'Cook Islands' to 'Cook Islands (NZ Protectorate)'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Its important to note that the Series returned when a DataFrame is indexed is merely a **view** on the DataFrame, and not a copy of the data itself. So you must be cautious when manipulating this data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "vals = data.population\n",
    "vals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "vals[5] = 0\n",
    "vals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "vals = data.population.copy()\n",
    "vals[5] = 1000\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can create or modify columns by assignment:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.population[3] = 1\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.population[3] = 447000\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Deleting rows (records) based on conditions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Previous examples demonstrated how to remove entire columns from a data frame and how to modify individual data points in records. \n",
    "\n",
    "Frequently we would like to remove select rows that meet a given requirement.\n",
    "\n",
    "In the below example we will delete all rows where the value of population is bellow 1 million. \n",
    "\n",
    "There are 2 steps we must perform in order to do this. We first identify all the rows that we want removed and create another data frame from this where the index value is preserved."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "indexOfRowsToDrop = data[data.population < 1000000]\n",
    "indexOfRowsToDrop"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we call the *drop()* method on the target data frame. The drop method requires a list of indices that need to be dropped form a data frame. We additionally indicate that the drop procedure should permanently delete the data by specifying inplace=True. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.drop(indexOfRowsToDrop.index, inplace=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Write code that deletes all records that are from year 2014 and have population greater than 4 million."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding new rows/records"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Appending new rows (records) in order to achieve enlargement of a data frame can be accomplished using the *.ix[]* (or .loc[]) attribute seen earlier, followed by the square brackets which specify the location of the insertion."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.loc[8] = [\"Germany\", 85000000, 2014, 'Europe', False]\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.loc[10] = [\"Germany\", 85000000, 2014, 'Europe', False]\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Write code that inserts a record for France (population 66m)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Cleaning - Practical Example"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Go to http://inflationdata.com/Inflation/Inflation_Rate/Historical_Oil_Prices_Table.asp \n",
    "\n",
    "Highlight and copy the data in the table found on this web site, from the column name 'Year', down to the end of the last record.\n",
    "\n",
    "One of the amazing features that Pandas has built in, is that we can directly import this into a data frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "oil_price = pd.read_clipboard()\n",
    "oil_price"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to fully appreciate the trends, we need to plot this data. plt.plot() allows us to easily do this. It requires that we pass into this method numerical values for the x-axis, separated by the comma and followed by numerical values for the y-axis. \n",
    "\n",
    "However, this is what happens when we try this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "oil_price.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.plot(oil_price['Year '], oil_price['Inflation Adjusted Price'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " **Exercise**: Write code that finds out what data type the two columns are that we are trying to plot."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Due to the presence of the '$' symbol. pandas has interpreted this column as a string. \n",
    "\n",
    " **Exercise**: Write code that removes the '$' symbol from all he entries and converts the column into a float. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    " **Exercise**:Write code that generates two plots. One for the Inflation Adjusted Price of oil per year and the other for the Nominal Price of oil per year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise: "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once you have plotted the data, save the dataframe as a .csv file called 'oil_prices.csv'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
